#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : 全球法律在线项目                #
#                                                                   #
#                   @File Name    : ExportNews.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2020-12-11 10:44                 #
#                                                                   #
#                   @Last Update  : 2020-12-11 10:44                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes:该类用于导出新闻数据                                       #
#                                                                   #
#-------------------------------------------------------------------#
"""
import datetime
import hashlib
import os
import re

import pymysql

from DatabaseControl.databasecontrol import MysqlDatabaseControl
from DateProcessFunction.datefunction import Timefunction
from LawNewsExport.DFAFilter import DFAFilter
from settings import LawNews_CJ_MYSQL, LawNews_Target_MYSQL


class ExportNews:
    # 初始化方法
    def __init__(self):
        self.fielddict = {"新闻标题": 'Title', "发表时间": 'PublishDate', "国别": 'SortA', "语言": 'SortB',
                          "新闻全文": 'Fulltexthtml', "原文网址": 'DownLoadUrl', "下载网站中文名称": 'DownLoadWebNameC',
                          "下载网站英文名称": 'DownLoadWebNameE', "网站类别": "Website"}
        self.gfw = DFAFilter()
        # 加载敏感词库的敏感词数据
        path = "敏感词库"
        for file in os.listdir(path):
            file_path = os.path.join(path, file)
            if os.path.exists(file_path):
                self.gfw.parse(file_path)

    def export_news(self):
        """
        将采集的新闻数据导入到目标新闻数据表中
        """
        lawnews_tables = MysqlDatabaseControl().Gettablename(LawNews_CJ_MYSQL)  # 获取采集的新闻数据库中的所有表
        for lawnews_table in lawnews_tables:
            self.export_table(lawnews_table)

    def export_table(self, tablename):
        """
        将数据库表中的数据导入到目标表
        import:tablename
        return:
        """
        # 采集数据库连接
        cjsqlconn = pymysql.connect(host=LawNews_CJ_MYSQL['MYSQL_HOST'], port=LawNews_CJ_MYSQL['MYSQL_PORT'],
                                    db=LawNews_CJ_MYSQL['MYSQL_DB'], user=LawNews_CJ_MYSQL['MYSQL_USER'],
                                    passwd=LawNews_CJ_MYSQL['MYSQL_PASSWD'], charset='utf8')

        # 目标表的Sql语句列表
        targetsqllist = []      # 需要向目标表中插入记录的语句列表
        urllist = []        # 需要向目标表中插入记录对应的网址列表
        # 获取采集数据表中的所有字段名称
        cjsqlcorsor = cjsqlconn.cursor()  # 获取采集数据库游标
        sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '%s' and table_schema = '%s';" \
              % (tablename, LawNews_CJ_MYSQL['MYSQL_DB'])
        try:
            cjsqlcorsor.execute(sql)
            cjtablefiles = cjsqlcorsor.fetchall()
            cjtablefilelist = []    # 采集表对应的字段列表
            for cjtablefile in cjtablefiles:
                cjtablefilelist.append(cjtablefile[0])
            # 表中没有数据状态字段则新增该字段并默认为0
            if "数据状态" not in cjtablefilelist:
                sql = "ALTER TABLE %s ADD 数据状态 VARCHAR(255) DEFAULT '0'" % tablename
                try:
                    cjsqlcorsor.execute(sql)
                    cjsqlconn.commit()
                    cjtablefilelist.append("数据状态")
                except Exception as e:
                    print(str(tablename) + "中新增数据状态字段出错:" + str(e))
            # 查询采集表中未入库的数据
            sql = "select 原文网址 FROM %s where 数据状态 = '0';" % tablename
            try:
                cjsqlcorsor.execute(sql)
                downurls = cjsqlcorsor.fetchall()
                for downurl in downurls:
                    # 按照原文网址字段依次查询每条记录
                    dowloadurl = pymysql.escape_string(downurl[0])
                    sql = "select * FROM %s where 原文网址 = '%s'" % (tablename, dowloadurl)
                    try:
                        cjsqlcorsor.execute(sql)
                        newsdata = cjsqlcorsor.fetchall()
                    except Exception as e:
                        print("查询%s的%s网址详细信息出错%s" % (tablename,dowloadurl,str(e)))
                        continue
                    newsdatadict = {}
                    for i in range(len(cjtablefilelist)):
                        newsdatadict[cjtablefilelist[i]] = newsdata[0][i]
                    targetdict = self.deal_newsdata(newsdatadict)
                    if targetdict == 555:
                        with open('dateeorr.txt', 'a', encoding='utf-8') as f:
                            f.write("%s表%s的日期错误" % (tablename, dowloadurl)  + '\n')
                        continue
                    if targetdict == 111:
                        with open('sensitiveerro.txt', 'a', encoding='utf-8') as f:
                            f.write("%s表%s的全文有敏感词" % (tablename, dowloadurl)  + '\n')
                        sql = '''UPDATE %s set 数据状态 = "2" where 原文网址 = "%s"''' % (tablename, dowloadurl)
                        try:
                            cjsqlcorsor.execute(sql)
                            cjsqlconn.commit()
                        except Exception as e:
                            print("修改%s的%s网址敏感词数据状态出错%s" % (tablename,dowloadurl,str(e)))
                        continue
                    # 获取字典中的字段名和字段值
                    targetfilelist = list(targetdict.keys())
                    targetfilestr = ",".join(targetfilelist)
                    targetvaluelist = list(targetdict.values())
                    targetvalue = tuple(targetvaluelist)
                    # 向目标表中插入记录的语句列表
                    targetsql = '''insert into %s (%s) values %s''' % (
                        LawNews_Target_MYSQL["MYSQL_TABLE"], targetfilestr, targetvalue)
                    targetsqllist.append(targetsql)
                    urllist.append(dowloadurl)
            except Exception as e:
                print("sql查询出错:%s" % sql + str(e))
        except Exception as e:
            print("sql查询出错:%s" % sql + str(e))
        if len(targetsqllist):
            # 目标数据库连接
            targetsqlconn = pymysql.connect(host=LawNews_Target_MYSQL['MYSQL_HOST'],
                                            port=LawNews_Target_MYSQL['MYSQL_PORT'],
                                            db=LawNews_Target_MYSQL['MYSQL_DB'],
                                            user=LawNews_Target_MYSQL['MYSQL_USER'],
                                            passwd=LawNews_Target_MYSQL['MYSQL_PASSWD'], charset='utf8')
            targetsqlcorsor = targetsqlconn.cursor()
            print("正在导出" + str(tablename) + "表的数据")
            for i in range(len(targetsqllist)):
                try:
                    targetsqlcorsor.execute(targetsqllist[i])
                    cjsql = '''UPDATE %s SET 数据状态 = '1' WHERE 原文网址 = '%s' ''' % (
                        tablename, urllist[i])
                    try:
                        cjsqlcorsor.execute(cjsql)
                        targetsqlconn.commit()
                        cjsqlconn.commit()
                    except Exception as e:
                        print(str(tablename) + '表修改数据状态出错：%s(%s)' % (str(e),cjsql))
                except Exception as e:
                    print(str(LawNews_Target_MYSQL["MYSQL_TABLE"]) + '数据表存入数据出错：' + str(e))
            targetsqlcorsor.close()
            targetsqlconn.close()
        cjsqlcorsor.close()
        cjsqlconn.close()

    def deal_newsdata(self, datadict: dict):
        """
        将一条采集的新闻记录转为目标表的记录
        import: datadict 一条采集的新闻记录
        return: result_dict 一条目标表的新闻记录字典
        """
        result_dict = {}
        fieldlist = list(self.fielddict.keys())
        datalist = list(datadict.keys())
        for field in fieldlist:
            if field in datalist:
                if field == "发表时间":
                    resultvaule = Timefunction().strpdatetime(datadict[field])
                    if resultvaule == 555:
                        return 555
                    result_dict[self.fielddict[field]] = resultvaule
                # elif field == "新闻全文":  # 对图片无格式的富文本添加style="width: 100%;格式
                #     image_regular = r'''<img.+?/>'''  # 匹配图片标签的正则表达式
                #     image_trs = re.findall(image_regular, datadict[field], re.S)
                #     for image_tr in image_trs:
                #         if ("style" not in image_tr) and ("width" not in image_tr):
                #             new_image_tr = image_tr.replace(r'''/>''', r''' style="width: 100%;"/>''')
                #             datadict[field] = datadict[field].replace(image_tr, new_image_tr)
                #     result_dict[self.fielddict[field]] = datadict[field]
                elif field == "新闻全文":   # 进行敏感词过滤
                    result = self.gfw.filter(datadict[field])
                    # if result:
                    #     print(str(datetime.datetime.now()) + ":"+result + "是敏感词")
                    #     with open("sensitiveerro.txt", "a", encoding='utf-8') as f:
                    #         f.write(str(datetime.datetime.now()) + ":"+ result + ":")
                    #     return 111
                    result_dict[self.fielddict[field]] = datadict[field]
                else:
                    result_dict[self.fielddict[field]] = datadict[field]
        result_dict["SYSID"] = str(hashlib.md5(datadict["原文网址"].encode('utf-8')).hexdigest())
        return result_dict
